package dao;

import db_util.DBUtil;
import entity.User;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created with IntelliJ IDEA.
 * Description:
 * User: ZhangYufei
 * Date: 2021-07-21
 * Time: 13:46
 */
public class UserDao {
    //添加一个新的用户，注册的时候
    public void insert(User user) {
        Connection connection = DBUtil.getConnection();
        String sql = "insert into user(username,password) values(?,?)";
        PreparedStatement statement = null;
        try {
            statement = connection.prepareStatement(sql);
            statement.setString(1, user.getUsername());
            statement.setString(2, user.getPassword());
            statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection, statement, null);
        }
    }

    //删除一个用户(根据用户id)，管理员权限
    public void delete(int userId) {
        Connection connection = DBUtil.getConnection();
        String sql = "delete from user where user_id = ?";
        PreparedStatement statement = null;
        try {
            statement = connection.prepareStatement(sql);
            statement.setInt(1, userId);
            statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection, statement, null);
        }
    }

    //显示所有用户，管理员权限
    public List<User> selectAll() {
        List<User> list = new ArrayList<>();
        Connection connection = DBUtil.getConnection();
        String sql = "select * from user";
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            statement = connection.prepareStatement(sql);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                User user = new User();
                user.setId(resultSet.getInt("user_id"));
                user.setUsername(resultSet.getString("username"));
                user.setPassword(resultSet.getString("password"));
                list.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection, statement, resultSet);
        }
        return list;
    }

    //根据用户id查找用户
    public User selectById(int userId) {
        Connection connection = DBUtil.getConnection();
        String sql = "select * from user where user_id = ?";
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            statement = connection.prepareStatement(sql);
            statement.setInt(1, userId);
            resultSet = statement.executeQuery();
            if (resultSet.next()) {
                User user = new User();
                user.setId(resultSet.getInt("user_id"));
                user.setUsername(resultSet.getString("username"));
                user.setPassword(resultSet.getString("password"));
                return user;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection, statement, resultSet);
        }
        return null;
    }

    //登录时，查找数据库
    public User selectByName(String username) {
        Connection connection = DBUtil.getConnection();
        String sql = "select * from user where username = ?";
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            statement = connection.prepareStatement(sql);
            statement.setString(1, username);
            resultSet = statement.executeQuery();
            if (resultSet.next()) {
                User user = new User();
                user.setId(resultSet.getInt("user_id"));
                user.setUsername(resultSet.getString("username"));
                user.setPassword(resultSet.getString("password"));
                return user;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection, statement, resultSet);
        }
        return null;
    }

    public static void main(String[] args) {
        /*//验证添加新用户
        User user = new User();
        user.setUsername("root");
        user.setPassword("123");
        UserDao userDao = new UserDao();
        userDao.insert(user);*/

        /*//验证查找
        UserDao userDao = new UserDao();
        //查找所有
        List<User> list = userDao.selectAll();
        System.out.println(list);
        //根据username查找
        User user = userDao.selectByName("root");
        System.out.println(user);
        //根据userId查找
        User user = userDao.selectById(1);
        System.out.println(user);*/

        /*//验证删除
        UserDao userDao = new UserDao();
        userDao.delete(1);*/
    }
}
